--------------------------------------------------------------------------------
-- @name: tpack_server_package.sql
-- @author: dion cho
-- @note: source for tpack_server package
-- @todo :

--------------------------------------------------------------------------------
--spool tpack_server.txt
set echo on
set verify on
set scan off

drop package tpack_server;


-- create package spec
create or replace package tpack_server authid definer is

  --------------------------------------
  -- constants
  --------------------------------------
  TPACK_SERVER_MODULE     varchar2(100) := 'TPACK_SERVER$$';
  
  
  --------------------------------------
  -- configuration
  --------------------------------------
  g_svr_ctrl    tpack_server_control%rowtype;
  g_instance_no number;
  
  --------------------------------------
  -- exception
  --------------------------------------
  INVALID_REPORT_TYPE_ERR_NO  number := -20051;
  INVALID_REPORT_TYPE_ERR_MSG varchar2(1000) :=
      'Report type is not existent';
  
  REPORT_NOT_EXISTS_ERR_NO    number := -20052;
  REPORT_NOT_EXISTS_ERR_MSG   varchar2(1000) := 
      'Report does not exist';
      
  PARAM_NOT_EXISTS_ERR_NO     number := -20053;
  PARAM_NOT_EXISTS_ERR_MSG    varchar2(1000) :=
      'Parameter does not exit';
  
  INVALID_COND_TYPE_ERR_NO    number := -20054;
  INVALID_COND_TYPE_ERR_MSG   varchar2(1000) := 
      'Invalid report condition type. It should be one of SESSION, STAT and WAIT';
      
  INVALID_COND_MEASURE_ERR_NO  number := -20055;
  INVALID_COND_MEASURE_ERR_MSG  varchar2(1000) :=
      'Invalid report measure type. It should be one of DELTA and SUM';
      
  SERVER_IS_RUNNING_ERR_NO    number := -20056;
  SERVER_IS_RUNNING_ERR_MSG   varchar2(1000) :=
      'Server is at running status';
      
  NO_JOB_QUEUE_ERR_NO           number := -20057;
  NO_JOB_QUEUE_ERR_MSG          varchar2(1000) := 
      'JOB_QUEUE_PROCESSES is 0. Increase the value';

  SERVER_HAS_STOPPED_ERR_NO     number := -20058;
  SERVER_HAS_STOPPED_ERR_MSG    varchar2(1000) :=
      'Server has alreay stopped';
      
      
  PROC_SID_NOT_EXISTS_ERR_NO    number := -20059;
  PROC_SID_NOT_EXISTS_ERR_MSG   varchar2(1000) :=
      'SESSION_ID is not existent in the procedure';
  
  PROC_NOT_EXISTS_ERR_NO         number := -20060;
  PROC_NOT_EXISTS_ERR_MSG         varchar2(1000) := 
      'Procedure is not existent';
  
  INV_PARAM_TYPE_ERR_NO           number := -20061;
  INV_PARAM_TYPE_ERR_MSG          varchar2(1000) :=
      'Invalid parameter type. It should be one of number and varchar2';
  
  PARAM_DUPLICATE_ERR_NO          number := -20062;
  PARAM_DUPLICATE_ERR_MSG         varchar2(1000) :=
      'Dupliate parameter name';
      
      
  -- report job related types
  type report_template_array is table of report_template_type;
  type report_param_templ_array is table of report_param_template_type;
  type report_param_list_array is table of report_param_list_type;
  type active_report_job_type_array is table of active_report_job_type;
  
  
  ------------------------------------------------------------------------------
  -- set debugging mode
  -- @param     mode      BASIC or DETAIL
  ------------------------------------------------------------------------------
  procedure set_debug(dmode varchar2);
  

  ------------------------------------------------------------------------------
  -- Get available report types
  -- @return The array of report_template_type
  ------------------------------------------------------------------------------
  function report_templates 
    return report_template_array
    pipelined;
    
  ------------------------------------------------------------------------------
  -- Get available parameters of a specific report
  -- @param     report_type   varchar2    report type
  -- @return The array of report_param_template_type
  ------------------------------------------------------------------------------
  function report_param_templates(
        report_type     in varchar2) 
    return report_param_templ_array
    pipelined;
    
  
  ------------------------------------------------------------------------------
  -- Get available parameter values of a specific report (for list type)
  -- @param     report_type   varchar2    report_tpe
  -- @param     param_name    varchar2    parameter name
  -- @return The array of report_param_list_type
  ------------------------------------------------------------------------------
  function report_param_lists(
        report_type     in varchar2,
        param_name      in varchar2) 
    return report_param_list_array
    pipelined;
    
    
    
  
  ------------------------------------------------------------------------------
  -- Create custom report type
  -- @param     report_type   varchar2    report type to create
  -- @param     procedure_name  varchar2  the procedure name to executed when this the report is executed
  ------------------------------------------------------------------------------
  procedure add_report_template (
        report_type       in varchar2,
        procedure_name    in varchar2);
        
  
  ------------------------------------------------------------------------------
  -- Delete custom report type
  -- @param     report_type     varchar2    report type to be deleted
  ------------------------------------------------------------------------------
  procedure delete_report_template (
        report_type       in varchar2);
    
  
  ------------------------------------------------------------------------------
  -- Add custom report template parameter
  ------------------------------------------------------------------------------
  procedure add_report_param_template(
        report_type       in varchar2, 
        param_name        in varchar2,
        param_type        in varchar2, --number, varchar2
        default_value     in varchar2);
        
        
  ------------------------------------------------------------------------------
  -- Create new report job and return the id
  -- @param   report_type   varchar2    The report type. One of the values returned by report_templates
  -- @param   occurrence    number      how many times to execute? default = 1
  -- @param   delay_time    number      how long to delay the execution? default = 0
  -- @param   start_time    date        when the report to be activated? default = sysdate
  -- @param   is_enabled    number      is report enabled? default = 1. 1 = enabled, 0 = disabled
  -- @return  unique report id
  ------------------------------------------------------------------------------
  function create_report (
        report_type   in varchar2,
        occurrence    in number default 1,
        delay_time    in number default 1,
        start_time    in date default sysdate,
        is_enabled       in number default 1)
    return varchar2
  ;
  
  ------------------------------------------------------------------------------
  -- Delete report of the given report id
  -- @param   report_id   varchar2    The ID of the report to be deleted
  -- @param   b_include_job_hist  boolean include job history? default = true
  ------------------------------------------------------------------------------
  procedure delete_report(
        report_id   in varchar2,
        b_include_job_hist  in boolean default true)
  ;
  
  ------------------------------------------------------------------------------
  -- Modify report of the given report id
  -- @param   report_id   varchar2    The ID of the report to be deleted
  -- @param   report_type   varchar2    The report type. One of the values returned by report_templates
  -- @param   occurrence    number      how many times to execute? default = 1, null = infinitive
  -- @param   delay_time    number      how long to delay the execution? default = 0
  -- @param   start_time    date        when the report to be activated? default = sysdate
  -- @param   is_enabled    number      is report enabled? default = 1. 1 = enabled, 0 = disabled
  ------------------------------------------------------------------------------
  procedure modify_report(
        report_id   in varchar2,
        report_type   in varchar2 default null,
        occurrence    in number default null,
        delay_time    in number default null,
        start_time    in date default null,
        is_enabled    in number default null);
  
  
  ------------------------------------------------------------------------------
  -- Add report parameter
  -- @param   report_id     The ID of the report
  -- @param   param_name    The name of the parameter. ex) session_id
  -- @param   param_value   The value of the parameter. ex) > 100
  ------------------------------------------------------------------------------
  procedure add_parameter(
      report_id  in varchar2,
      param_name  in varchar2,
      param_value in varchar2)
  ;
  
  
  ------------------------------------------------------------------------------
  -- Add report collection condition
  -- @param   report_id   The report id returned by create_report
  -- @param   type        The condition type. STAT = session stat, WAIT = wait, SESSION = v$session
  -- @param   name        The collection item name. ex) "session logical reads", "session pga memory"
  -- @param   value       When the value meets this criteria...  ex) "> 10000"
  -- @param   measure     How to measure the value. DELTA, SUM
  ------------------------------------------------------------------------------
  procedure add_condition(
      report_id   in varchar2,
      type    in varchar2,
      name    in varchar2,
      value   in varchar2,
      measure in varchar2)
  ;
  
  
  ------------------------------------------------------------------------------
  -- Register the report job
  -- @param   report_id   The report id returned by create_report
  ------------------------------------------------------------------------------
  procedure register_report (
        report_id   in varchar2);
        
  
  ------------------------------------------------------------------------------
  -- Execute the report and store the result to table
  -- @param   report_id   The report id to be executed
  -- @param   session_id  The session id for which to report should be collected. default = current session
  ------------------------------------------------------------------------------
  procedure execute_report(
        report_id   in varchar2,
        session_id  in number default userenv('sid'));
  
  
 
  ------------------------------------------------------------------------------
  -- Enable/Disable report 
  -- @param     report_id   varchar2    The id of the report to enable/disable
  -- @param     bEnable     boolean     true = enable, false = disable
  ------------------------------------------------------------------------------
  procedure enable_report(report_id in varchar2, bEnable boolean) 
  ;
  
  
  ------------------------------------------------------------------------------
  -- Get active report jobs 
  ------------------------------------------------------------------------------
  function active_report_jobs
    return active_report_job_type_array
    pipelined;
    
  
  ------------------------------------------------------------------------------
  -- Kill job
  -- call this procedure instead of killing the session yourself.
  ------------------------------------------------------------------------------
  procedure kill_job(job_id in varchar2);
  
  
  ------------------------------------------------------------------------------
  -- Get detail report information
  -- including report type, parameter, condition, job history, stored result 
  ------------------------------------------------------------------------------
  function report_detail(report_id in varchar2) 
    return tpack.varchar2_array
    pipelined;
  
  
  
  ------------------------------------------------------------------------------
  -- Purge all reports
  ------------------------------------------------------------------------------
  procedure purge_reports;
  
  
  ------------------------------------------------------------------------------
  -- Purge all report job history
  ------------------------------------------------------------------------------
  procedure purge_report_job_hist;
  
  
  ------------------------------------------------------------------------------
  -- Start job server
  ------------------------------------------------------------------------------
  procedure start_server;
  
  ------------------------------------------------------------------------------
  -- Stop job server
  ------------------------------------------------------------------------------
  procedure stop_server;
  
  ------------------------------------------------------------------------------
  -- Is server started?
  -- @return  1 = started, 0 = stopped
  ------------------------------------------------------------------------------
  function server_running
    return number;
  
  ------------------------------------------------------------------------------
  -- Terminate server. Call this procedure when the server is in abnormal status
  ------------------------------------------------------------------------------
  procedure terminate_server;
  
  ------------------------------------------------------------------------------
  -- Internal procedure to execute conditional report colletion. 
  -- Do not call this procedure yourself!
  -- It should be run by tpack_server.start_server procedure.
  ------------------------------------------------------------------------------
  procedure internal_server_logic
  ;
  
end tpack_server;
/

show errors

--spool off
set scan on
--ed tpack_server.txt


